<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
	<title>Date/Time and Interval Functions and Operators | ElasticSearch 7.7 权威指南中文版</title>
	<meta name="keywords" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <meta name="description" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <!-- Give IE8 a fighting chance -->
    <!--[if lt IE 9]>
    <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
    <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
	<link rel="stylesheet" type="text/css" href="../static/styles.css" />
	<script>
	var _link = 'sql-functions-datetime.html';
    </script>
</head>
<body>
<div class="main-container">
    <section id="content">
        <div class="content-wrapper">
            <section id="guide" lang="zh_cn">
                <div class="container">
                    <div class="row">
                        <div class="col-xs-12 col-sm-8 col-md-8 guide-section">
                            <div style="color:gray; word-break: break-all; font-size:12px;">原英文版地址: <a href="https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-functions-datetime.html" rel="nofollow" target="_blank">https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-functions-datetime.html</a>, 原文档版权归 www.elastic.co 所有<br/>本地英文版地址: <a href="../en/sql-functions-datetime.html" rel="nofollow" target="_blank">../en/sql-functions-datetime.html</a></div>
                        <!-- start body -->
                  <div class="page_header">
<strong>重要</strong>: 此版本不会发布额外的bug修复或文档更新。最新信息请参考 <a href="https://www.elastic.co/guide/en/elasticsearch/reference/current/index.html" rel="nofollow">当前版本文档</a>。
</div>
<div id="content">
<div class="breadcrumbs">
<span class="breadcrumb-link"><a href="index.html">Elasticsearch Guide [7.7]</a></span>
»
<span class="breadcrumb-link"><a href="xpack-sql.html">SQL access</a></span>
»
<span class="breadcrumb-link"><a href="sql-functions.html">Functions and Operators</a></span>
»
<span class="breadcrumb-node">Date/Time and Interval Functions and Operators</span>
</div>
<div class="navheader">
<span class="prev">
<a href="sql-functions-grouping.html">« Grouping Functions</a>
</span>
<span class="next">
<a href="sql-functions-search.html">Full-Text Search Functions »</a>
</span>
</div>
<div class="section xpack">
<div class="titlepage"><div><div>
<h2 class="title">
<a id="sql-functions-datetime"></a>Date/Time and Interval Functions and Operators<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a><a class="xpack_tag" href="https://www.elastic.co/subscriptions"></a>
</h2>
</div></div></div>
<p>Elasticsearch SQL offers a wide range of facilities for performing date/time manipulations.</p>
<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-interval"></a>Intervals<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p>A common requirement when dealing with date/time in general revolves around
the notion of <code class="literal">interval</code>, a topic that is worth exploring in the context of Elasticsearch and Elasticsearch SQL.</p>
<p>Elasticsearch has comprehensive support for <a class="xref" href="common-options.html#date-math" title="Date Math">date math</a> both inside <a class="xref" href="date-math-index-names.html" title="Date math support in index names">index names</a> and <a class="xref" href="mapping-date-format.html" title="format">queries</a>.
Inside Elasticsearch SQL the former is supported as is by passing the expression in the table name, while the latter is supported through the standard SQL <code class="literal">INTERVAL</code>.</p>
<p>The table below shows the mapping between Elasticsearch and Elasticsearch SQL:</p>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
<col class="col_2">
</colgroup>
<tbody>
<tr>
<td align="center" valign="top"><p><span class="strong strong"><strong>Elasticsearch</strong></span></p></td>
<td align="center" valign="top"><p><span class="strong strong"><strong>Elasticsearch SQL</strong></span></p></td>
</tr>
<tr>
<td align="center" colspan="2" valign="top"><p><span class="strong strong"><strong>Index/Table datetime math</strong></span></p></td>
</tr>
<tr>
<td align="center" colspan="2" valign="top"><p><code class="literal">&lt;index-{now/M{YYYY.MM}}&gt;</code></p></td>
</tr>
<tr>
<td align="center" colspan="2" valign="top"><p><span class="strong strong"><strong>Query date/time math</strong></span></p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">1y</code></p></td>
<td align="center" valign="top"><p><code class="literal">INTERVAL 1 YEAR</code></p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">2M</code></p></td>
<td align="center" valign="top"><p><code class="literal">INTERVAL 2 MONTH</code></p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">3w</code></p></td>
<td align="center" valign="top"><p><code class="literal">INTERVAL 21 DAY</code></p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">4d</code></p></td>
<td align="center" valign="top"><p><code class="literal">INTERVAL 4 DAY</code></p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">5h</code></p></td>
<td align="center" valign="top"><p><code class="literal">INTERVAL 5 HOUR</code></p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">6m</code></p></td>
<td align="center" valign="top"><p><code class="literal">INTERVAL 6 MINUTE</code></p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">7s</code></p></td>
<td align="center" valign="top"><p><code class="literal">INTERVAL 7 SECOND</code></p></td>
</tr>
</tbody>
</table>
</div>
<p><code class="literal">INTERVAL</code> allows either <code class="literal">YEAR</code> and <code class="literal">MONTH</code> to be mixed together <em>or</em> <code class="literal">DAY</code>, <code class="literal">HOUR</code>, <code class="literal">MINUTE</code> and <code class="literal">SECOND</code>.</p>
<div class="tip admon">
<div class="icon"></div>
<div class="admon_content">
<p>Elasticsearch SQL accepts also the plural for each time unit (e.g. both <code class="literal">YEAR</code> and <code class="literal">YEARS</code> are valid).</p>
</div>
</div>
<p>Example of the possible combinations below:</p>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
<col class="col_2">
</colgroup>
<tbody>
<tr>
<td align="center" valign="top"><p><span class="strong strong"><strong>Interval</strong></span></p></td>
<td align="center" valign="top"><p><span class="strong strong"><strong>Description</strong></span></p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">INTERVAL '1-2' YEAR TO MONTH</code></p></td>
<td align="center" valign="top"><p>1 year and 2 months</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">INTERVAL '3 4' DAYS TO HOURS</code></p></td>
<td align="center" valign="top"><p>3 days and 4 hours</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">INTERVAL '5 6:12' DAYS TO MINUTES</code></p></td>
<td align="center" valign="top"><p>5 days, 6 hours and 12 minutes</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">INTERVAL '3 4:56:01' DAY TO SECOND</code></p></td>
<td align="center" valign="top"><p>3 days, 4 hours, 56 minutes and 1 second</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">INTERVAL '2 3:45:01.23456789' DAY TO SECOND</code></p></td>
<td align="center" valign="top"><p>2 days, 3 hours, 45 minutes, 1 second and 234567890 nanoseconds</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">INTERVAL '123:45' HOUR TO MINUTES</code></p></td>
<td align="center" valign="top"><p>123 hours and 45 minutes</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">INTERVAL '65:43:21.0123' HOUR TO SECONDS</code></p></td>
<td align="center" valign="top"><p>65 hours, 43 minutes, 21 seconds and 12300000 nanoseconds</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">INTERVAL '45:01.23' MINUTES TO SECONDS</code></p></td>
<td align="center" valign="top"><p>45 minutes, 1 second and 230000000 nanoseconds</p></td>
</tr>
</tbody>
</table>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="_operators"></a>Operators<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p>Basic arithmetic operators (<code class="literal">+</code>, <code class="literal">-</code>, <code class="literal">*</code>) support date/time parameters as indicated below:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT INTERVAL 1 DAY + INTERVAL 53 MINUTES AS result;

    result
---------------
+1 00:53:00</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CAST('1969-05-13T12:34:56' AS DATETIME) + INTERVAL 49 YEARS AS result;

       result
--------------------
2018-05-13T12:34:56Z</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT - INTERVAL '49-1' YEAR TO MONTH result;

    result
---------------
-49-1</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT INTERVAL '1' DAY - INTERVAL '2' HOURS AS result;

    result
---------------
+0 22:00:00</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CAST('2018-05-13T12:34:56' AS DATETIME) - INTERVAL '2-8' YEAR TO MONTH AS result;

       result
--------------------
2015-09-13T12:34:56Z</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT -2 * INTERVAL '3' YEARS AS result;

    result
---------------
-6-0</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="_functions"></a>Functions<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p>Functions that target date/time.</p>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-current-date"></a><code class="literal">CURRENT_DATE/CURDATE</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">CURRENT_DATE
CURRENT_DATE()
CURDATE()</pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>: <em>none</em></p>
<p><span class="strong strong"><strong>Output</strong></span>: date</p>
<p><span class="strong strong"><strong>Description</strong></span>: Returns the date (no time part) when the current query reached the server.
It can be used both as a keyword: <code class="literal">CURRENT_DATE</code> or as a function with no arguments: <code class="literal">CURRENT_DATE()</code>.</p>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>Unlike CURRENT_DATE, <code class="literal">CURDATE()</code> can only be used as a function with no arguments and not as a keyword.</p>
</div>
</div>
<p>This method always returns the same value for its every occurrence within the same query.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CURRENT_DATE AS result;

         result
------------------------
2018-12-12</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CURRENT_DATE() AS result;

         result
------------------------
2018-12-12</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CURDATE() AS result;

         result
------------------------
2018-12-12</pre>
</div>
<p>Typically, this function (as well as its twin <a class="xref" href="sql-functions-datetime.html#sql-functions-today" title="TODAY">TODAY())</a> function
is used for relative date filtering:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT first_name FROM emp WHERE hire_date &gt; TODAY() - INTERVAL 35 YEARS ORDER BY first_name ASC LIMIT 5;

 first_name
------------
Alejandro
Amabile
Anneke
Anoosh
Arumugam</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-current-time"></a><code class="literal">CURRENT_TIME/CURTIME</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">CURRENT_TIME
CURRENT_TIME([precision]) <a id="CO92-1"></a><i class="conum" data-value="1"></i>
CURTIME</pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO92-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>fractional digits; optional</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: time</p>
<p><span class="strong strong"><strong>Description</strong></span>: Returns the time when the current query reached the server.
As a function, <code class="literal">CURRENT_TIME()</code> accepts <em>precision</em> as an optional
parameter for rounding the second fractional digits (nanoseconds). The default <em>precision</em> is 3,
meaning a milliseconds precision current time will be returned.</p>
<p>This method always returns the same value for its every occurrence within the same query.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CURRENT_TIME AS result;

         result
------------------------
12:31:27.237Z</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CURRENT_TIME() AS result;

         result
------------------------
12:31:27.237Z</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CURTIME() AS result;

         result
------------------------
12:31:27.237Z</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CURRENT_TIME(1) AS result;

         result
------------------------
12:31:27.2Z</pre>
</div>
<p>Typically, this function is used for relative date/time filtering:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT first_name FROM emp WHERE CAST(hire_date AS TIME) &gt; CURRENT_TIME() - INTERVAL 20 MINUTES ORDER BY first_name ASC LIMIT 5;

  first_name
---------------
Alejandro
Amabile
Anneke
Anoosh
Arumugam</pre>
</div>
<div class="important admon">
<div class="icon"></div>
<div class="admon_content">
<p>Currently, using a <em>precision</em> greater than 3 doesn’t make any difference to the output of the
function as the maximum number of second fractional digits returned is 3 (milliseconds).</p>
</div>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-current-timestamp"></a><code class="literal">CURRENT_TIMESTAMP</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision]) <a id="CO93-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO93-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>fractional digits; optional</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: date/time</p>
<p><span class="strong strong"><strong>Description</strong></span>: Returns the date/time when the current query reached the server.
As a function, <code class="literal">CURRENT_TIMESTAMP()</code> accepts <em>precision</em> as an optional
parameter for rounding the second fractional digits (nanoseconds). The default <em>precision</em> is 3,
meaning a milliseconds precision current date/time will be returned.</p>
<p>This method always returns the same value for its every occurrence within the same query.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CURRENT_TIMESTAMP AS result;

         result
------------------------
2018-12-12T14:48:52.448Z</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CURRENT_TIMESTAMP() AS result;

         result
------------------------
2018-12-12T14:48:52.448Z</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CURRENT_TIMESTAMP(1) AS result;

         result
------------------------
2018-12-12T14:48:52.4Z</pre>
</div>
<p>Typically, this function (as well as its twin <a class="xref" href="sql-functions-datetime.html#sql-functions-now" title="NOW">NOW())</a> function is used for
relative date/time filtering:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT first_name FROM emp WHERE hire_date &gt; NOW() - INTERVAL 100 YEARS ORDER BY first_name ASC LIMIT 5;

  first_name
---------------
Alejandro
Amabile
Anneke
Anoosh
Arumugam</pre>
</div>
<div class="important admon">
<div class="icon"></div>
<div class="admon_content">
<p>Currently, using a <em>precision</em> greater than 3 doesn’t make any difference to the output of the
function as the maximum number of second fractional digits returned is 3 (milliseconds).</p>
</div>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-add"></a><code class="literal">DATE_ADD/DATEADD/TIMESTAMP_ADD/TIMESTAMPADD</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">DATE_ADD(
    string_exp, <a id="CO94-1"></a><i class="conum" data-value="1"></i>
    integer_exp, <a id="CO94-2"></a><i class="conum" data-value="2"></i>
    datetime_exp) <a id="CO94-3"></a><i class="conum" data-value="3"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO94-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>string expression denoting the date/time unit to add to the date/datetime</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO94-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>integer expression denoting how many times the above unit should be added to/from the date/datetime,
if a negative value is used it results to a subtraction from the date/datetime</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO94-3"><i class="conum" data-value="3"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: datetime</p>
<p><span class="strong strong"><strong>Description</strong></span>: Add the given number of date/time units to a date/datetime. If the number of units is negative then it’s subtracted from
the date/datetime. If any of the three arguments is <code class="literal">null</code> a <code class="literal">null</code> is returned.</p>
<div class="warning admon">
<div class="icon"></div>
<div class="admon_content">
<p>If the second argument is a long there is possibility of truncation since an integer value will be extracted and
used from that long.</p>
</div>
</div>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
<col class="col_2">
</colgroup>
<thead>
<tr>
<th align="center" colspan="2" valign="top">Datetime units to add/subtract</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center" valign="top"><p><span class="strong strong"><strong>unit</strong></span></p></td>
<td align="center" valign="top"><p><span class="strong strong"><strong>abbreviations</strong></span></p></td>
</tr>
<tr>
<td align="center" valign="top"><p>year</p></td>
<td align="center" valign="top"><p>years, yy, yyyy</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>quarter</p></td>
<td align="center" valign="top"><p>quarters, qq, q</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>month</p></td>
<td align="center" valign="top"><p>months, mm, m</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>dayofyear</p></td>
<td align="center" valign="top"><p>dy, y</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>day</p></td>
<td align="center" valign="top"><p>days, dd, d</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>week</p></td>
<td align="center" valign="top"><p>weeks, wk, ww</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>weekday</p></td>
<td align="center" valign="top"><p>weekdays, dw</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>hour</p></td>
<td align="center" valign="top"><p>hours, hh</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>minute</p></td>
<td align="center" valign="top"><p>minutes, mi, n</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>second</p></td>
<td align="center" valign="top"><p>seconds, ss, s</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>millisecond</p></td>
<td align="center" valign="top"><p>milliseconds, ms</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>microsecond</p></td>
<td align="center" valign="top"><p>microseconds, mcs</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>nanosecond</p></td>
<td align="center" valign="top"><p>nanoseconds, ns</p></td>
</tr>
</tbody>
</table>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_ADD('years', 10, '2019-09-04T11:22:33.000Z'::datetime) AS "+10 years";

      +10 years
------------------------
2029-09-04T11:22:33.000Z</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_ADD('week', 10, '2019-09-04T11:22:33.000Z'::datetime) AS "+10 weeks";

      +10 weeks
------------------------
2019-11-13T11:22:33.000Z</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_ADD('seconds', -1234, '2019-09-04T11:22:33.000Z'::datetime) AS "-1234 seconds";

      -1234 seconds
------------------------
2019-09-04T11:01:59.000Z</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_ADD('qq', -417, '2019-09-04'::date) AS "-417 quarters";

      -417 quarters
------------------------
1915-06-04T00:00:00.000Z</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_ADD('minutes', 9235, '2019-09-04'::date) AS "+9235 minutes";

      +9235 minutes
------------------------
2019-09-10T09:55:00.000Z</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-diff"></a><code class="literal">DATE_DIFF/DATEDIFF/TIMESTAMP_DIFF/TIMESTAMPDIFF</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">DATE_DIFF(
    string_exp, <a id="CO95-1"></a><i class="conum" data-value="1"></i>
    datetime_exp, <a id="CO95-2"></a><i class="conum" data-value="2"></i>
    datetime_exp) <a id="CO95-3"></a><i class="conum" data-value="3"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO95-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>string expression denoting the date/time unit difference between the following two date/datetime expressions</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO95-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>start date/datetime expression</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO95-3"><i class="conum" data-value="3"></i></a></p>
</td>
<td align="left" valign="top">
<p>end date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: integer</p>
<p><span class="strong strong"><strong>Description</strong></span>: Subtract the second argument from the third argument and return their difference in multiples of the unit
specified in the first argument. If the second argument (start) is greater than the third argument (end),
then negative values are returned. If any of the three arguments is <code class="literal">null</code>, a <code class="literal">null</code> is returned.</p>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
<col class="col_2">
</colgroup>
<thead>
<tr>
<th align="center" colspan="2" valign="top">Datetime difference units</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center" valign="top"><p><span class="strong strong"><strong>unit</strong></span></p></td>
<td align="center" valign="top"><p><span class="strong strong"><strong>abbreviations</strong></span></p></td>
</tr>
<tr>
<td align="center" valign="top"><p>year</p></td>
<td align="center" valign="top"><p>years, yy, yyyy</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>quarter</p></td>
<td align="center" valign="top"><p>quarters, qq, q</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>month</p></td>
<td align="center" valign="top"><p>months, mm, m</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>dayofyear</p></td>
<td align="center" valign="top"><p>dy, y</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>day</p></td>
<td align="center" valign="top"><p>days, dd, d</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>week</p></td>
<td align="center" valign="top"><p>weeks, wk, ww</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>weekday</p></td>
<td align="center" valign="top"><p>weekdays, dw</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>hour</p></td>
<td align="center" valign="top"><p>hours, hh</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>minute</p></td>
<td align="center" valign="top"><p>minutes, mi, n</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>second</p></td>
<td align="center" valign="top"><p>seconds, ss, s</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>millisecond</p></td>
<td align="center" valign="top"><p>milliseconds, ms</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>microsecond</p></td>
<td align="center" valign="top"><p>microseconds, mcs</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>nanosecond</p></td>
<td align="center" valign="top"><p>nanoseconds, ns</p></td>
</tr>
</tbody>
</table>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_DIFF('years', '2019-09-04T11:22:33.000Z'::datetime, '2032-09-04T22:33:11.000Z'::datetime) AS "diffInYears";

      diffInYears
------------------------
13</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_DIFF('week', '2019-09-04T11:22:33.000Z'::datetime, '2016-12-08T22:33:11.000Z'::datetime) AS "diffInWeeks";

      diffInWeeks
------------------------
-143</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_DIFF('seconds', '2019-09-04T11:22:33.123Z'::datetime, '2019-07-12T22:33:11.321Z'::datetime) AS "diffInSeconds";

      diffInSeconds
------------------------
-4625362</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_DIFF('qq', '2019-09-04'::date, '2025-04-25'::date) AS "diffInQuarters";

      diffInQuarters
------------------------
23</pre>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>For <code class="literal">hour</code> and <code class="literal">minute</code>, <code class="literal">DATEDIFF</code> doesn’t do any rounding, but instead first truncates
the more detailed time fields on the 2 dates to zero and then calculates the subtraction.</p>
</div>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATEDIFF('hours', '2019-11-10T12:10:00.000Z'::datetime, '2019-11-10T23:59:59.999Z'::datetime) AS "diffInHours";

      diffInHours
------------------------
11</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATEDIFF('minute', '2019-11-10T12:10:00.000Z'::datetime, '2019-11-10T12:15:59.999Z'::datetime) AS "diffInMinutes";

      diffInMinutes
------------------------
5</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_DIFF('minutes', '2019-09-04'::date, '2015-08-17T22:33:11.567Z'::datetime) AS "diffInMinutes";

      diffInMinutes
------------------------
-2128407</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-part"></a><code class="literal">DATE_PART/DATEPART</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">DATE_PART(
    string_exp, <a id="CO96-1"></a><i class="conum" data-value="1"></i>
    datetime_exp) <a id="CO96-2"></a><i class="conum" data-value="2"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO96-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>string expression denoting the unit to extract from the date/datetime</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO96-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: integer</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract the specified unit from a date/datetime. If any of the two arguments is <code class="literal">null</code> a <code class="literal">null</code> is returned.
It’s similar to <a class="xref" href="sql-functions-datetime.html#sql-functions-datetime-extract" title="EXTRACT"><code class="literal">EXTRACT</code></a> but with different names and aliases for the units and
provides more options (e.g.: <code class="literal">TZOFFSET</code>).</p>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
<col class="col_2">
</colgroup>
<thead>
<tr>
<th align="center" colspan="2" valign="top">Datetime units to extract</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center" valign="top"><p><span class="strong strong"><strong>unit</strong></span></p></td>
<td align="center" valign="top"><p><span class="strong strong"><strong>abbreviations</strong></span></p></td>
</tr>
<tr>
<td align="center" valign="top"><p>year</p></td>
<td align="center" valign="top"><p>years, yy, yyyy</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>quarter</p></td>
<td align="center" valign="top"><p>quarters, qq, q</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>month</p></td>
<td align="center" valign="top"><p>months, mm, m</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>dayofyear</p></td>
<td align="center" valign="top"><p>dy, y</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>day</p></td>
<td align="center" valign="top"><p>days, dd, d</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>week</p></td>
<td align="center" valign="top"><p>weeks, wk, ww</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>weekday</p></td>
<td align="center" valign="top"><p>weekdays, dw</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>hour</p></td>
<td align="center" valign="top"><p>hours, hh</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>minute</p></td>
<td align="center" valign="top"><p>minutes, mi, n</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>second</p></td>
<td align="center" valign="top"><p>seconds, ss, s</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>millisecond</p></td>
<td align="center" valign="top"><p>milliseconds, ms</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>microsecond</p></td>
<td align="center" valign="top"><p>microseconds, mcs</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>nanosecond</p></td>
<td align="center" valign="top"><p>nanoseconds, ns</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>tzoffset</p></td>
<td align="center" valign="top"><p>tz</p></td>
</tr>
</tbody>
</table>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_PART('year', '2019-09-22T11:22:33.123Z'::datetime) AS "years";

   years
----------
2019</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_PART('mi', '2019-09-04T11:22:33.123Z'::datetime) AS mins;

   mins
-----------
22</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_PART('quarters', CAST('2019-09-24' AS DATE)) AS quarter;

   quarter
-------------
3</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_PART('month', CAST('2019-09-24' AS DATE)) AS month;

   month
-------------
9</pre>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>For <code class="literal">week</code> and <code class="literal">weekday</code> the unit is extracted using the non-ISO calculation, which means
that a given week is considered to start from Sunday, not Monday.</p>
</div>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_PART('week', '2019-09-22T11:22:33.123Z'::datetime) AS week;

   week
----------
39</pre>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>The <code class="literal">tzoffset</code> returns the total number of minutes (signed) that represent the time zone’s offset.</p>
</div>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_PART('tzoffset', '2019-09-04T11:22:33.123+05:15'::datetime) AS tz_mins;

   tz_mins
--------------
315</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_PART('tzoffset', '2019-09-04T11:22:33.123-03:49'::datetime) AS tz_mins;

   tz_mins
--------------
-229</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-trunc"></a><code class="literal">DATE_TRUNC/DATETRUNC</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">DATE_TRUNC(
    string_exp, <a id="CO97-1"></a><i class="conum" data-value="1"></i>
    datetime_exp/interval_exp) <a id="CO97-2"></a><i class="conum" data-value="2"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO97-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>string expression denoting the unit to which the date/datetime/interval should be truncated to</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO97-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime/interval expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: datetime/interval</p>
<p><span class="strong strong"><strong>Description</strong></span>: Truncate the date/datetime/interval to the specified unit by setting all fields that are less significant than the specified
one to zero (or one, for day, day of week and month). If any of the two arguments is <code class="literal">null</code> a <code class="literal">null</code> is returned.
If the first argument is <code class="literal">week</code> and the second argument is of <code class="literal">interval</code> type, an error is thrown since the <code class="literal">interval</code> data type doesn’t support a <code class="literal">week</code> time unit.</p>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
<col class="col_2">
</colgroup>
<thead>
<tr>
<th align="center" colspan="2" valign="top">Datetime truncation units</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center" valign="top"><p><span class="strong strong"><strong>unit</strong></span></p></td>
<td align="center" valign="top"><p><span class="strong strong"><strong>abbreviations</strong></span></p></td>
</tr>
<tr>
<td align="center" valign="top"><p>millennium</p></td>
<td align="center" valign="top"><p>millennia</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>century</p></td>
<td align="center" valign="top"><p>centuries</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>decade</p></td>
<td align="center" valign="top"><p>decades</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>year</p></td>
<td align="center" valign="top"><p>years, yy, yyyy</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>quarter</p></td>
<td align="center" valign="top"><p>quarters, qq, q</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>month</p></td>
<td align="center" valign="top"><p>months, mm, m</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>week</p></td>
<td align="center" valign="top"><p>weeks, wk, ww</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>day</p></td>
<td align="center" valign="top"><p>days, dd, d</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>hour</p></td>
<td align="center" valign="top"><p>hours, hh</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>minute</p></td>
<td align="center" valign="top"><p>minutes, mi, n</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>second</p></td>
<td align="center" valign="top"><p>seconds, ss, s</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>millisecond</p></td>
<td align="center" valign="top"><p>milliseconds, ms</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>microsecond</p></td>
<td align="center" valign="top"><p>microseconds, mcs</p></td>
</tr>
<tr>
<td align="center" valign="top"><p>nanosecond</p></td>
<td align="center" valign="top"><p>nanoseconds, ns</p></td>
</tr>
</tbody>
</table>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_TRUNC('millennium', '2019-09-04T11:22:33.123Z'::datetime) AS millennium;

      millennium
------------------------
2000-01-01T00:00:00.000Z</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATETRUNC('week', '2019-08-24T11:22:33.123Z'::datetime) AS week;

      week
------------------------
2019-08-19T00:00:00.000Z</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_TRUNC('mi', '2019-09-04T11:22:33.123Z'::datetime) AS mins;

      mins
------------------------
2019-09-04T11:22:00.000Z</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_TRUNC('decade', CAST('2019-09-04' AS DATE)) AS decades;

      decades
------------------------
2010-01-01T00:00:00.000Z</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATETRUNC('quarters', CAST('2019-09-04' AS DATE)) AS quarter;

      quarter
------------------------
2019-07-01T00:00:00.000Z</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_TRUNC('centuries', INTERVAL '199-5' YEAR TO MONTH) AS centuries;

      centuries
------------------
 +100-0</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_TRUNC('hours', INTERVAL '17 22:13:12' DAY TO SECONDS) AS hour;

      hour
------------------
+17 22:00:00</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DATE_TRUNC('days', INTERVAL '19 15:24:19' DAY TO SECONDS) AS day;

      day
------------------
+19 00:00:00</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-day"></a><code class="literal">DAY_OF_MONTH/DOM/DAY</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">DAY_OF_MONTH(datetime_exp) <a id="CO98-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO98-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: integer</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract the day of the month from a date/datetime.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DAY_OF_MONTH(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
19</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-dow"></a><code class="literal">DAY_OF_WEEK/DAYOFWEEK/DOW</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">DAY_OF_WEEK(datetime_exp) <a id="CO99-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO99-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: integer</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract the day of the week from a date/datetime. Sunday is <code class="literal">1</code>, Monday is <code class="literal">2</code>, etc.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DAY_OF_WEEK(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
2</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-doy"></a><code class="literal">DAY_OF_YEAR/DOY</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">DAY_OF_YEAR(datetime_exp) <a id="CO100-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO100-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: integer</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract the day of the year from a date/datetime.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DAY_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
50</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-dayname"></a><code class="literal">DAY_NAME/DAYNAME</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">DAY_NAME(datetime_exp) <a id="CO101-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO101-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: string</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract the day of the week from a date/datetime in text format (<code class="literal">Monday</code>, <code class="literal">Tuesday</code>…​).</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DAY_NAME(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
Monday</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-hour"></a><code class="literal">HOUR_OF_DAY/HOUR</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">HOUR_OF_DAY(datetime_exp) <a id="CO102-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO102-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: integer</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract the hour of the day from a date/datetime.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT HOUR_OF_DAY(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS hour;

     hour
---------------
10</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-isodow"></a><code class="literal">ISO_DAY_OF_WEEK/ISODAYOFWEEK/ISODOW/IDOW</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">ISO_DAY_OF_WEEK(datetime_exp) <a id="CO103-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO103-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: integer</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract the day of the week from a date/datetime, following the <a href="https://en.wikipedia.org/wiki/ISO_week_date" class="ulink" target="_top">ISO 8601 standard</a>.
Monday is <code class="literal">1</code>, Tuesday is <code class="literal">2</code>, etc.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT ISO_DAY_OF_WEEK(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
1</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-isoweek"></a><code class="literal">ISO_WEEK_OF_YEAR/ISOWEEKOFYEAR/ISOWEEK/IWOY/IW</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">ISO_WEEK_OF_YEAR(datetime_exp) <a id="CO104-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO104-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: integer</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract the week of the year from a date/datetime, following <a href="https://en.wikipedia.org/wiki/ISO_week_date" class="ulink" target="_top">ISO 8601 standard</a>. The first week
of a year is the first week with a majority (4 or more) of its days in January.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT ISO_WEEK_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS week;

     week
---------------
8</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-minuteofday"></a><code class="literal">MINUTE_OF_DAY</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">MINUTE_OF_DAY(datetime_exp) <a id="CO105-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO105-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: integer</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract the minute of the day from a date/datetime.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT MINUTE_OF_DAY(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS minute;

    minute
---------------
623</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-minute"></a><code class="literal">MINUTE_OF_HOUR/MINUTE</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">MINUTE_OF_HOUR(datetime_exp) <a id="CO106-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO106-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: integer</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract the minute of the hour from a date/datetime.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT MINUTE_OF_HOUR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS minute;

    minute
---------------
23</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-month"></a><code class="literal">MONTH_OF_YEAR/MONTH</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">MONTH(datetime_exp) <a id="CO107-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO107-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: integer</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract the month of the year from a date/datetime.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT MONTH_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS month;

     month
---------------
2</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-monthname"></a><code class="literal">MONTH_NAME/MONTHNAME</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">MONTH_NAME(datetime_exp) <a id="CO108-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO108-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: string</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract the month from a date/datetime in text format (<code class="literal">January</code>, <code class="literal">February</code>…​).</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT MONTH_NAME(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS month;

     month
---------------
February</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-now"></a><code class="literal">NOW</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">NOW()</pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>: <em>none</em></p>
<p><span class="strong strong"><strong>Output</strong></span>: datetime</p>
<p><span class="strong strong"><strong>Description</strong></span>: This function offers the same functionality as <a class="xref" href="sql-functions-datetime.html#sql-functions-current-timestamp" title="CURRENT_TIMESTAMP">CURRENT_TIMESTAMP()</a> function: returns
the datetime when the current query reached the server. This method always returns the same value for its every
occurrence within the same query.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT NOW() AS result;

         result
------------------------
2018-12-12T14:48:52.448Z</pre>
</div>
<p>Typically, this function (as well as its twin <a class="xref" href="sql-functions-datetime.html#sql-functions-current-timestamp" title="CURRENT_TIMESTAMP">CURRENT_TIMESTAMP())</a> function is used
for relative date/time filtering:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT first_name FROM emp WHERE hire_date &gt; NOW() - INTERVAL 100 YEARS ORDER BY first_name ASC LIMIT 5;

  first_name
---------------
Alejandro
Amabile
Anneke
Anoosh
Arumugam</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-second"></a><code class="literal">SECOND_OF_MINUTE/SECOND</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SECOND_OF_MINUTE(datetime_exp) <a id="CO109-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO109-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: integer</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract the second of the minute from a date/datetime.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT SECOND_OF_MINUTE(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS second;

    second
---------------
27</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-quarter"></a><code class="literal">QUARTER</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">QUARTER(datetime_exp) <a id="CO110-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO110-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: integer</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract the year quarter the date/datetime falls in.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT QUARTER(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS quarter;

    quarter
---------------
1</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-today"></a><code class="literal">TODAY</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">TODAY()</pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>: <em>none</em></p>
<p><span class="strong strong"><strong>Output</strong></span>: date</p>
<p><span class="strong strong"><strong>Description</strong></span>: This function offers the same functionality as <a class="xref" href="sql-functions-datetime.html#sql-functions-current-date" title="CURRENT_DATE/CURDATE">CURRENT_DATE()</a> function: returns
the date when the current query reached the server. This method always returns the same value for its every occurrence
within the same query.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT TODAY() AS result;

         result
------------------------
2018-12-12</pre>
</div>
<p>Typically, this function (as well as its twin <a class="xref" href="sql-functions-datetime.html#sql-functions-current-timestamp" title="CURRENT_TIMESTAMP">CURRENT_TIMESTAMP())</a> function is used
for relative date filtering:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT first_name FROM emp WHERE hire_date &gt; TODAY() - INTERVAL 35 YEARS ORDER BY first_name ASC LIMIT 5;

 first_name
------------
Alejandro
Amabile
Anneke
Anoosh
Arumugam</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-week"></a><code class="literal">WEEK_OF_YEAR/WEEK</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">WEEK_OF_YEAR(datetime_exp) <a id="CO111-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO111-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: integer</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract the week of the year from a date/datetime.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT WEEK(CAST('1988-01-05T09:22:10Z' AS TIMESTAMP)) AS week, ISOWEEK(CAST('1988-01-05T09:22:10Z' AS TIMESTAMP)) AS isoweek;

      week     |   isoweek
---------------+---------------
2              |1</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-year"></a><code class="literal">YEAR</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">YEAR(datetime_exp) <a id="CO112-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO112-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: integer</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract the year from a date/datetime.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS year;

     year
---------------
2018</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-datetime-extract"></a><code class="literal">EXTRACT</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/date-time.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">EXTRACT(
    datetime_function  <a id="CO113-1"></a><i class="conum" data-value="1"></i>
    FROM datetime_exp) <a id="CO113-2"></a><i class="conum" data-value="2"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO113-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/time function name</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO113-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/datetime expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: integer</p>
<p><span class="strong strong"><strong>Description</strong></span>: Extract fields from a date/datetime by specifying the name of a <a class="xref" href="sql-functions-datetime.html" title="Date/Time and Interval Functions and Operators">datetime function</a>.
The following</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT EXTRACT(DAY_OF_YEAR FROM CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
50</pre>
</div>
<p>is the equivalent to</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT DAY_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
50</pre>
</div>
</div>

</div>
<div class="navfooter">
<span class="prev">
<a href="sql-functions-grouping.html">« Grouping Functions</a>
</span>
<span class="next">
<a href="sql-functions-search.html">Full-Text Search Functions »</a>
</span>
</div>
</div>

                  <!-- end body -->
                        </div>
                        <div class="col-xs-12 col-sm-4 col-md-4" id="right_col">
                        
                        </div>
                    </div>
                </div>
            </section>
        </div>
    </section>
</div>
<script src="../static/cn.js"></script>
</body>
</html>